package com.scalar.forecast.forecastReports.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.ScalarException;
import com.scalar.forecast.systemprefs.SystemPreferences;
import com.scalar.forecast.systemprefs.dao.SystemPrefsDAO;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.forecast.salesForecastEntry.RowData;
import com.scalar.forecast.salesForecastEntry.SlotData;
import com.scalar.forecast.forecastReports.forms.SalesForecastReportForm;
import com.scalar.forecast.forecastReports.AnalysisReport;
import com.scalar.forecast.forecastReports.WaterFallChart;

import org.apache.struts.util.LabelValueBean;

import java.util.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;

/**
 * Created by Veeru.
 * Date: Jul 27, 2005
 * Time: 1:47:36 PM
 */

public class ForecastReportsDAO {

    /**
     * Method to load all the products
     *
     * @param dbTransaction
     * @return products
     * @throws ScalarException
     */
    public static ArrayList getAllProducts( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList products = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success = false;
        String partNumberType =  null;

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
        if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
               partNumberType = "PART_NUMBER";
        } else{
               partNumberType = "P_MARKETING_PART_NUMBER";
        }

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            labelValueBean = new LabelValueBean ( "-----CHIP-----","0");
            products.add( labelValueBean );

            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get( "forecast.forecastReports.getChips" ) );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( partNumberType ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }
            pstmt.close();
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.forecastReports.getBoardsOrKits" ) );

            //to load BOARDS
            pstmt.setString ( 1, "BOARD" );
            labelValueBean = new LabelValueBean ( "-----BOARD-----","0");
            products.add( labelValueBean );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( partNumberType ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }

            //to load KITS
            pstmt.setString ( 1, "KIT" );
            labelValueBean = new LabelValueBean ( "-----KIT-----","0");
            products.add( labelValueBean );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( partNumberType ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            System.out.println ( "----Exception while getting all the products-----");
            throw new ScalarException ( "Could Not Get All the Products" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException e ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException e ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException e ) { }
            }
        }
        return products;
    }
    /**
     * Method to load all the customers
     *
     * @param dbTransaction
     * @return
     * @throws ScalarException
     */
    public static ArrayList getAllCustomers ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList customers = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success =false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.forecastReports.getAllCustomers" ) );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "C_Name"), rset.getString ( "Customer_ID") );
                customers.add ( labelValueBean );
            }
            success =true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while fetching customers ---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load Customers" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close();} catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return customers;
    }


    public static ArrayList getSalesPersonsWithManagerList(int salesPersonId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesPersonList = new ArrayList();

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.loadSubordinateSalespersonsWithManager"));
            pstmt.setInt ( 1, salesPersonId );
            pstmt.setInt ( 2, salesPersonId );
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String salesPerson_Id = rs.getString("SalesPerson_Id");
                String salesPerson_Name = rs.getString("SalesPerson_Name");

                salesPersonList.add(new org.apache.struts.util.LabelValueBean(salesPerson_Name, salesPerson_Id));
            }
        } catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Sales Persons from database", sqlex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException excp) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException excp) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException excp) {
                }
            }
        }

        return salesPersonList;
    }


     public static ArrayList loadOdmList() throws ScalarException
       {
           Connection conn = null;
           PreparedStatement pstmt = null;
           ResultSet rs = null;
           ArrayList Odms = new ArrayList ();

           try
           {
               conn = DataSource.getConnection ();
               pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get("forecast.reports.loadAllOdms") );

               rs = pstmt.executeQuery ();
               while ( rs.next () )
               {
                   String odm_name = rs.getString ( "C_Name" );
                   String odm_id = rs.getString ( "Customer_ID" );
                   Odms.add ( new org.apache.struts.util.LabelValueBean ( odm_name, odm_id ) );
               }
           }
           catch ( Exception sqlex )
           {
               sqlex.printStackTrace ();
               throw new ScalarException ( "Could not get End Users from database", sqlex );
           }
           finally
           {
               if ( rs != null )
               {
                   try
                   {
                       rs.close ();
                   }
                   catch ( SQLException excp )
                   {
                   }
               }
               if ( pstmt != null )
               {
                   try
                   {
                       pstmt.close ();
                   }
                   catch ( SQLException excp )
                   {
                   }
               }
               if ( conn != null )
               {
                   try
                   {
                       conn.close ();
                   }
                   catch ( SQLException excp )
                   {
                   }
               }
           }
           return Odms;
       }
     
     public static LinkedList getSalesForecastReportRowDataList ( DBTransaction dbTransaction, int customerId, int endCustomerId, int productId, int salesPersonId, float confidenceLevel, String period,
                                                                 int qtrNo, int yearFrom, int monthFrom, int showNext, String category, String reportType, String userList1Val_input ) throws ScalarException
     {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        LinkedHashMap map = new LinkedHashMap();
        RowData rowData = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall ( "{CALL SP_SALES_FORECAST_EXCEL_REPORT( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}" );
            cstmt.setInt ( 1, customerId );
            cstmt.setInt ( 2, endCustomerId );
            cstmt.setInt( 3, productId );
            cstmt.setInt( 4, salesPersonId );
            cstmt.setFloat( 5, confidenceLevel );
            cstmt.setString ( 6, period );
            cstmt.setInt ( 7, qtrNo );
            cstmt.setInt ( 8, yearFrom );
            cstmt.setInt ( 9, monthFrom );
            cstmt.setInt ( 10, showNext );
            cstmt.setString( 11, category );
            cstmt.setString( 12, reportType );
            cstmt.setString ( 13, userList1Val_input );

            rset = cstmt.executeQuery();
            while ( rset.next() )
            {
                String customerName = rset.getString ( "C_NAME" );
                String endCustomerName = rset.getString ( "END_CUSTOMER" );
                String salesPersonName = rset.getString ( "SALESPERSON_NAME" );
                String projectName = rset.getString ( "PROJNAME" );
                String partNumber = rset.getString ( "P_MARKETING_PART_NUMBER" );
                String userList1Val = rset.getString ( "USERLIST1VAL" );
                String userList2Val = rset.getString ( "USERLIST2VAL" );
                int month = rset.getInt ( "MONTH" );
                int year = rset.getInt ( "YEAR" );
                int qty = rset.getInt ( "QTY" );
                double price = rset.getDouble( "PRICE" );
                String endUserName = rset.getString("ENDUSERNAME");
                String userTxt1Val = rset.getString("USERTXT1VAL");
                String key = projectName + "_" + partNumber;
                if ( map.containsKey( key ) )
                {
                    rowData = (RowData) map.get( key );
                }
                else
                {
                    rowData = new RowData();
                    rowData.setCustomerName( customerName );
                    rowData.setEndCustomerName( endCustomerName );
                    rowData.setSalesPersonName( salesPersonName );
                    rowData.setProjName( projectName );
                    rowData.setProductName( partNumber );
                    rowData.setUserList1Val( userList1Val );
                    rowData.setUserList2Val( userList2Val );
                    rowData.setEndUserName(endUserName);
                    rowData.setUserDefined1(userTxt1Val);
                    map.put( key, rowData );
                }
                SlotData slotData = rowData.getSlotData( month, year );
                if ( slotData == null )
                {
                    slotData = new SlotData();
                    slotData.setMonth( month );
                    slotData.setYear( year );
                    slotData.setQty( qty );
                    slotData.setPrice( price );
                    rowData.addSlotData ( slotData );
                }
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Forecast Report Details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignroe ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return new LinkedList( map.values() );
    }
     
     public static LinkedList getSalesForecastReportRowDataListForAnalysis () throws ScalarException
     {
		Connection conn = null;
		CallableStatement cstmt = null;
		ResultSet rset = null;
		LinkedHashMap map = new LinkedHashMap();
		RowData rowData = null;
		try
		{
			conn = DataSource.getConnection();

			cstmt = conn.prepareCall ( "{CALL SP_SALES_FORECAST_ANALYSIS_REPORT()}" );
			rset = cstmt.executeQuery();
			while ( rset.next() )
			{
				int customerId = rset.getInt( "Customer_ID" );
				String customerName = rset.getString ( "C_NAME" );
				String endCustomerName = rset.getString ( "END_CUSTOMER" );
				String salesPersonName = rset.getString ( "SALESPERSON_NAME" );
				String projectName = rset.getString ( "PROJNAME" );
				int productId = rset.getInt( "ProcessStep_Id" );
				int parentProductId = rset.getInt( "P_Parent_Product_ID" );
				String partNumber = rset.getString ( "P_MARKETING_PART_NUMBER" );
				int salesPersonId = rset.getInt( "SalesPerson_ID" );
				String userList1Val = rset.getString ( "USERLIST1VAL" );
				String userList2Val = rset.getString ( "USERLIST2VAL" );
				int month = rset.getInt ( "MONTH" );
				int year = rset.getInt ( "YEAR" );
				int qty = rset.getInt ( "QTY" );
				double price = rset.getDouble( "PRICE" );
				String key = projectName + "_" + partNumber;
				if ( map.containsKey( key ) )
				{
					rowData = (RowData) map.get( key );
				}
				else
				{
					rowData = new RowData();
					rowData.setCustId( customerId );
					rowData.setCustomerName( customerName );
					rowData.setEndCustomerName( endCustomerName );
					rowData.setSalesPersonName( salesPersonName );
					rowData.setProjName( projectName );
					rowData.setProductId( productId );
					rowData.setBaseProductId( parentProductId );
					rowData.setProductName( partNumber );
					rowData.setSalesPersonId( salesPersonId );
					rowData.setUserList1Val( userList1Val );
					rowData.setUserList2Val( userList2Val );
					map.put( key, rowData );
				}
				SlotData slotData = rowData.getSlotData( month, year );
				if ( slotData == null )
				{
					slotData = new SlotData();
					slotData.setMonth( month );
					slotData.setYear( year );
					slotData.setQty( qty );
                    slotData.setAcQty( qty );
                    slotData.setPrice( price );
                    slotData.setAcPrice( price );
                    rowData.addSlotData ( slotData );
				}
			}
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException ( "Could not get Forecast Report Details", e );
		}
		finally
		{
			if ( rset != null )
			{
				try { rset.close(); } catch ( SQLException ignroe ) {}
			}
			if ( cstmt != null )
			{
				try { cstmt.close(); } catch ( SQLException ignore ) {}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch ( SQLException ignore ) {}
			}
		}
		return new LinkedList( map.values() );
     }     
 
    public static LinkedList getSalesForecastBacklogReportRowDataList ( DBTransaction dbTransaction, int customerId, int endCustomerId, int productId, int salesPersonId, float confidenceLevel, String period,
                                                                 int qtrNo, int yearFrom, int monthFrom, int showNext, String category, String reportType ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        RowData rowData = null;
        LinkedHashMap map = new LinkedHashMap();
        ArrayList rowDataArray = new ArrayList();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall ( "{CALL SP_NEW_SALES_FORECAST_BACKLOG_REPORT( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}" );
            cstmt.setInt ( 1, endCustomerId );
            cstmt.setInt ( 2, productId );
            cstmt.setInt ( 3, salesPersonId );
            cstmt.setInt ( 4, qtrNo );
            cstmt.setInt ( 5, yearFrom );
            cstmt.setString ( 6, category );
            cstmt.setString (7, "0");
            cstmt.setFloat  ( 8, confidenceLevel );
            cstmt.setString ( 9, period );
            cstmt.setInt    ( 10, monthFrom );
            cstmt.setInt    ( 11, showNext );
            cstmt.setString ( 12, customerId+"" );
            rset = cstmt.executeQuery();

            while ( rset.next() )
            {
                String customerName=rset.getString ( "CUSTOMER" ) ;
                String projectName = rset.getString ( "PROJECT" );
                String partNumber= rset.getString ( "PRODUCT" );
                String endCustomerName = rset.getString ( "ODM" );
                int month = rset.getInt ( "MONTH" );
                int year = rset.getInt ( "YEAR" );
                int fcQty= rset.getInt ("FC_QUANTITY");
                float fcPrice= rset.getFloat("FC_PRICE");
                int blQty= rset.getInt ("BL_QUANTITY");
                float blPrice = rset.getFloat("BL_PRICE");
                int acQty =rset.getInt ("ACTUAL_QUANTITY");
                float acPrice = rset.getFloat("ACTUAL_PRICE");
                String salesPersonName = rset.getString ( "SALES_PERSON");
                String key = null;
                if ("byEndCust".equalsIgnoreCase(category))
                {
                   key =  endCustomerName + "_" + month + "_" + year;
                }
                else if("byProduct".equalsIgnoreCase(category))
                {
                    key = partNumber+ "_" + month + "_" + year;
                }
                else if ("bySalesPerson".equalsIgnoreCase(category))
                {
                    key = salesPersonName+ "_" + month + "_" + year;
                }
                if ( map.containsKey( key ) )
                {
                    rowData = (RowData) map.get( key );
                }
                else
                {
                    rowData = new RowData();
                    rowData.setCustomerName( customerName );
                    rowData.setEndCustomerName( endCustomerName );
                    rowData.setSalesPersonName( salesPersonName );
                    rowData.setProjName( projectName );
                    rowData.setProductName( partNumber );
                    rowData.setMonth(month);
                    rowData.setYear(year);
                    map.put( key, rowData );
                }
                SlotData slotData = rowData.getSlotData( key, month, year );
                if ( slotData == null )
                {
                    slotData = new SlotData();
                    slotData.setMonth(month);
                    slotData.setYear(year);
                    slotData.setQty(fcQty);
                    slotData.setPrice(fcPrice);
                    slotData.setBlQty(blQty);
                    slotData.setBlPrice(blPrice);
                    slotData.setAcQty(acQty);
                    slotData.setAcPrice(acPrice);
                    slotData.setKey(key);
                    rowData.addSlotData ( slotData );
                }
                else
                {
                    if ( fcQty > 0 ) slotData.setQty(fcQty);
                    if ( fcPrice > 0 ) slotData.setPrice(fcPrice);
                    if ( blQty > 0) slotData.setBlQty(blQty);
                    if( blPrice > 0) slotData.setBlPrice(blPrice);
                    if( acQty> 0 ) slotData.setAcQty(acQty);
                    if( acPrice > 0)slotData.setAcPrice(acPrice);
                }
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Forecast Report Details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignroe ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
       }
       return new LinkedList( map.values());
    }
    public static Map getProjectUserDefinedFieldLables( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        Map map = new HashMap();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT TOP 1 USERLIST1, USERLIST2, USERLIST3, USERDATE1, USERDATE2, USERTXT1, USERTXT2, USERTXT3, USERTXT4, USERTXT5 FROM PROJSETUP" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                map.put( "USERLIST1", rset.getString ( "USERLIST1" ) );
                map.put( "USERLIST2", rset.getString ( "USERLIST2" ) );
                map.put( "USERLIST3", rset.getString ( "USERLIST3" ) );
                map.put( "USERDATE1", rset.getString ( "USERDATE1" ) );
                map.put( "USERDATE2", rset.getString ( "USERDATE2" ) );
                map.put( "USERTXT1", rset.getString ( "USERTXT1" ) );
                map.put( "USERTXT2", rset.getString ( "USERTXT2" ) );
                map.put( "USERTXT3", rset.getString ( "USERTXT3" ) );
                map.put( "USERTXT4", rset.getString ( "USERTXT4" ) );
                map.put( "USERTXT5", rset.getString ( "USERTXT5" ) );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load user defined fields labels",e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignroe ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return map;
    }

public static ArrayList getLeftToBookReport ( DBTransaction dbTransaction,  SalesForecastReportForm salesForecastReportForm, String reportType, String forecastType ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList leftToBookList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_LEFT_TO_BOOK_REPORT ( ?, ?, ?, ?, ?, ?, ?, ? ) }" );

            pstmt.setString( 1, salesForecastReportForm.getCustomerName() );
            pstmt.setString( 2, salesForecastReportForm.getSalesPartNumber() );
            pstmt.setString( 3, salesForecastReportForm.getChipSet() );
            pstmt.setString( 4, salesForecastReportForm.getChipFamily() );
            pstmt.setString( 5, reportType );
            pstmt.setString( 6, forecastType );
            pstmt.setInt( 7, salesForecastReportForm.getQtrNo() );
            pstmt.setInt( 8, salesForecastReportForm.getCurrentYear() );

            rset    = pstmt.executeQuery();

            leftToBookList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Left To Book report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return leftToBookList;
    }
    public static void saveForecastAnalysisReport ( DBTransaction dbTransaction,  String scenario, String reportName, String searchParams ) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT REPORT_NAME FROM FORECAST_ANALYSIS_REPORT_TABLE WHERE REPORT_NAME=?");

            pstmt.setString(1, reportName);
            rset = pstmt.executeQuery();
            if(rset.next()){
                throw new Exception("Forecast analysis report '" + reportName + "' already exists");
            }

            rset.close();
            rset = null;
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("INSERT INTO FORECAST_ANALYSIS_REPORT_TABLE( REPORT_NAME, REPORT_SEARCH_PARAMS, REPORT_SCENARIO, TIMESTAMP) VALUES ( ?, ?, ?, CURRENT_TIMESTAMP )");
            pstmt.setString ( 1, reportName );
            pstmt.setString ( 2, searchParams );
            pstmt.setString ( 3, scenario );

            pstmt.executeUpdate();

            if ( dbTransaction == null ) {
                conn.commit();
            }

            success = true;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException(e.getMessage(), e);
        }
        finally {
            if (dbTransaction == null) {
                if (conn != null) {
                    try {
                        if (!success) {
                            conn.rollback();
                        }
                    } catch (SQLException e) {
                    }
                }
            }
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    public static ArrayList<AnalysisReport> getForecastAnalysisReportsList ( DBTransaction dbTransaction,  String scenario, String reportName, String reportType, int fromYear, int toYear ) throws Exception {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        ArrayList<AnalysisReport> reportList    = new ArrayList<AnalysisReport>();
        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            String query;
            if("snapshot".equalsIgnoreCase(reportType)) {
                query = "SELECT REPORT_NAME, REPORT_SCENARIO, REPORT_TYPE, [TIMESTAMP] \n" +
                        "FROM (SELECT SNAPSHOT_NAME AS REPORT_NAME, 'snapshot' AS REPORT_SCENARIO, 'snapshot' AS REPORT_TYPE, MAX([TIMESTAMP]) AS [TIMESTAMP] FROM FORECAST_SNAPSHOT_TABLE GROUP BY SNAPSHOT_NAME) AS XYZ\n" +
                        "WHERE 1=1 ";
            } else if ( "analysis".equalsIgnoreCase(reportType)) {
                query = "SELECT REPORT_NAME, REPORT_SCENARIO, REPORT_TYPE, [TIMESTAMP] \n" +
                        "FROM (SELECT REPORT_NAME, REPORT_SCENARIO, 'analysis' AS REPORT_TYPE, [TIMESTAMP] FROM FORECAST_ANALYSIS_REPORT_TABLE) AS XYZ\n" +
                        "WHERE 1=1 ";
            } else {
                query = "SELECT REPORT_NAME, REPORT_SCENARIO, REPORT_TYPE, [TIMESTAMP] \n" +
                        "FROM (SELECT REPORT_NAME, REPORT_SCENARIO, 'analysis' AS REPORT_TYPE, [TIMESTAMP] FROM FORECAST_ANALYSIS_REPORT_TABLE\n" +
                        "UNION\n" +
                        "SELECT SNAPSHOT_NAME AS REPORT_NAME, 'snapshot' AS REPORT_SCENARIO, 'snapshot' AS REPORT_TYPE, MAX([TIMESTAMP]) FROM FORECAST_SNAPSHOT_TABLE GROUP BY SNAPSHOT_NAME) AS XYZ WHERE 1=1 ";
            }
            if(scenario != null && scenario.trim().length() > 0) {
                query += " AND XYZ.REPORT_SCENARIO = '" + scenario + "'";
            }
            if(reportName != null && reportName.trim().length() > 0) {
                query += " AND XYZ.REPORT_NAME LIKE '" + scenario + "%'";
            }
            if(fromYear >0) {
                query += " YEAR(XYZ.[TIMESTAMP]) >= " + fromYear ;
            }
            if(toYear >0) {
                query += " YEAR(XYZ.[TIMESTAMP]) <= " + toYear ;
            }


            pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();

            while(rset.next()){
                AnalysisReport analysisReport = new AnalysisReport();
                analysisReport.setReportName(rset.getString("REPORT_NAME"));
                analysisReport.setScenario(rset.getString("REPORT_SCENARIO"));
                analysisReport.setReprotType(rset.getString("REPORT_TYPE"));
                analysisReport.setTimestamp(rset.getDate("TIMESTAMP"));

                reportList.add(analysisReport);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException(e.getMessage(), e);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return reportList;
    }

    public static String getAnalysisParamStr(DBTransaction dbTransaction, String reportname) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String paramStr = null;
        try {
            if(dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT REPORT_SEARCH_PARAMS FROM FORECAST_ANALYSIS_REPORT_TABLE WHERE REPORT_NAME = ?");
            pstmt.setString(1, reportname);
            rset = pstmt.executeQuery();
            while (rset.next()) {
                paramStr = rset.getString("REPORT_SEARCH_PARAMS");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not get analysis param string", e);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignroe) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return paramStr;
    }

    /**
     * first element contains thm months header map.
     * @param snapshotname
     * @return
     * @throws ScalarException
     */
    public static LinkedList getSalesForecastSnapshotReportRowDataList(String snapshotname) throws ScalarException {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        LinkedHashMap map = new LinkedHashMap();
        RowData rowData = null;
        LinkedHashMap<String, String> monthsHeaderMap = new LinkedHashMap<String, String>();
        try {
            conn = DataSource.getConnection();

            cstmt = conn.prepareCall("{CALL SP_SALES_FORECAST_SNAPSHOT_REPORT(?)}");
            cstmt.setString(1, snapshotname);
            rset = cstmt.executeQuery();
            int idx = 0;
            while (rset.next()) {
                int customerId = rset.getInt("Customer_ID");
                String customerName = rset.getString("C_NAME");
                String endCustomerName = rset.getString("END_CUSTOMER");
                String salesPersonName = rset.getString("SALESPERSON_NAME");
                String projectName = rset.getString("PROJNAME");
                int productId = rset.getInt("ProcessStep_Id");
                int parentProductId = rset.getInt("P_Parent_Product_ID");
                String partNumber = rset.getString("P_MARKETING_PART_NUMBER");
                int salesPersonId = rset.getInt("SalesPerson_ID");
                String userList1Val = rset.getString("USERLIST1VAL");
                String userList2Val = rset.getString("USERLIST2VAL");
                int month = rset.getInt("MONTH");
                int year = rset.getInt("YEAR");

                if(!monthsHeaderMap.containsKey("Qty;"+month+";"+year)) {
                    monthsHeaderMap.put ( "Qty;"+month+";"+year, DateUtil.getShortMonthName(month)+"-" + year );
                    monthsHeaderMap.put ( "Price;"+month+";"+year, "Price" );
                    monthsHeaderMap.put ( "Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(month)+"-" + year + " Revenue" );
                    idx++;
                }
                

                int qty = rset.getInt("QTY");
                double price = rset.getDouble("PRICE");
                String key = projectName + "_" + partNumber;
                if (map.containsKey(key)) {
                    rowData = (RowData) map.get(key);
                } else {
                    rowData = new RowData();
                    rowData.setCustId(customerId);
                    rowData.setCustomerName(customerName);
                    rowData.setEndCustomerName(endCustomerName);
                    rowData.setSalesPersonName(salesPersonName);
                    rowData.setProjName(projectName);
                    rowData.setProductId(productId);
                    rowData.setBaseProductId(parentProductId);
                    rowData.setProductName(partNumber);
                    rowData.setSalesPersonId(salesPersonId);
                    rowData.setUserList1Val(userList1Val);
                    rowData.setUserList2Val(userList2Val);
                    rowData.setSnapshotName(snapshotname);
                    map.put(key, rowData);
                }
                SlotData slotData = rowData.getSlotData(month, year);
                if (slotData == null) {
                    slotData = new SlotData();
                    slotData.setMonth(month);
                    slotData.setYear(year);
                    slotData.setQty(qty);
                    slotData.setAcQty(qty);
                    slotData.setPrice(price);
                    slotData.setAcPrice(price);
                    rowData.addSlotData(slotData);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not get Snapshot Forecast Report Details", e);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignroe) {
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        LinkedList list = new LinkedList();
        list.add(monthsHeaderMap);
        list.addAll(map.values());
        return list;
    }

     public static LinkedList<WaterFallChart> getSalesForecastWaterFallChart(int projid, int prodid) throws ScalarException {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;

        LinkedList list = new LinkedList();
        try {
            conn = DataSource.getConnection();

            cstmt = conn.prepareCall("{CALL SP_FORECAST_WATER_FALL(?, ?)}");
            cstmt.setInt(1, projid);
            cstmt.setInt(2, prodid);
            rset = cstmt.executeQuery();
            while (rset.next()) {
                WaterFallChart waterFallChart = new WaterFallChart();
                waterFallChart.setLabel(rset.getString("MONTH_STR"));
                waterFallChart.setMonth1Qty(rset.getInt("MONTH1_QTY"));
                waterFallChart.setMonth2Qty(rset.getInt("MONTH2_QTY"));
                waterFallChart.setMonth3Qty(rset.getInt("MONTH3_QTY"));
                waterFallChart.setMonth4Qty(rset.getInt("MONTH4_QTY"));
                waterFallChart.setMonth5Qty(rset.getInt("MONTH5_QTY"));
                waterFallChart.setMonth6Qty(rset.getInt("MONTH6_QTY"));
                waterFallChart.setMonth7Qty(rset.getInt("MONTH7_QTY"));
                waterFallChart.setMonth8Qty(rset.getInt("MONTH8_QTY"));
                waterFallChart.setMonth9Qty(rset.getInt("MONTH9_QTY"));
                waterFallChart.setMonth10Qty(rset.getInt("MONTH10_QTY"));
                waterFallChart.setMonth11Qty(rset.getInt("MONTH11_QTY"));
                waterFallChart.setMonth12Qty(rset.getInt("MONTH12_QTY"));
                waterFallChart.setMonth13Qty(rset.getInt("MONTH13_QTY"));
                waterFallChart.setMonth14Qty(rset.getInt("MONTH14_QTY"));
                waterFallChart.setMonth15Qty(rset.getInt("MONTH15_QTY"));
                waterFallChart.setMonth16Qty(rset.getInt("MONTH16_QTY"));
                waterFallChart.setMonth17Qty(rset.getInt("MONTH17_QTY"));
                waterFallChart.setMonth18Qty(rset.getInt("MONTH18_QTY"));

                list.add(waterFallChart);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not get water fall chart Report Details", e);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignroe) {
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return list;
    }
}
